from fastapi import FastAPI, Form
from fastapi.middleware.cors import CORSMiddleware
from pydantic import BaseModel
from enum import Enum
import mysql.connector
from fastapi.staticfiles import StaticFiles
import json
import os
import pandas as pd
from numpy import nan


# 连接 mysql 数据库: gaokao
cnx = mysql.connector.connect(
    host="192.168.80.190",
    port=3306,
    user="root",
    password="12345678",
    database="gaokao",
    use_pure=True,
    ssl_disabled=True
)

# 连接 mysql 数据库: gaokao
def connect_sql():
    cnx = mysql.connector.connect(
        host="192.168.80.190",
        port=3306,
        user="root",
        password="12345678",
        database="gaokao",
        use_pure=True,
        ssl_disabled=True
    )
    return cnx


app = FastAPI()

# 存放学校logo在目录"home/static/logo" 下
app.mount("/images", StaticFiles(directory="/home/app/PySparkProject/images/download_logo"), name="images")
app.mount("/images_back", StaticFiles(directory="/home/app/PySparkProject/images/download_back"), name="images_back")

# 允许跨域请求，这里我们允许任何来源的请求，实际生产环境请设置具体的来源
app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)

adcodes = {
    "100000": "全国",
    "110000": "北京", "120000": "天津", "130000": "河北", "140000": "山西", "150000": "内蒙古", "210000": "辽宁",
    "220000": "吉林", "230000": "黑龙江", "310000": "上海", "320000": "江苏", "330000": "浙江", "340000": "安徽",
    "350000": "福建", "360000": "江西", "370000": "山东", "410000": "河南", "420000": "湖北", "430000": "湖南",
    "440000": "广东", "450000": "广西", "460000": "海南", "500000": "重庆", "510000": "四川", "520000": "贵州",
    "530000": "云南", "540000": "西藏", "610000": "陕西", "620000": "甘肃", "630000": "青海", "640000": "宁夏",
    "650000": "新疆", "710000": "台湾", "810000": "香港", "820000": "澳门"
}





################################################################################################################################
# ----------------------------------------------- 大数据面板 各省高考人数 ------------------------------------------------------ #
################################################################################################################################

@app.get("/adcode/{ad_code}/year/{year_}")
def get_score_by_province_and_year(ad_code: int, year_: int):
    """
        根据年份、省份获取高考人数
    """
    cnx = connect_sql()
    cursor = cnx.cursor()
    query_sql = ("SELECT exam_number FROM gaokao_number WHERE year = %s AND province = %s")
    data = (str(year_), adcodes.get(str(ad_code)))
    cursor.execute(query_sql, data)
    results = cursor.fetchone()
    cursor.close()
    cnx.close()

    if results != None:
        return results[0]
    else:
        return 0

################################################################################################################################
# ----------------------------------------------- 大数据面板 各省高考人数 ------------------------------------------------------ #
################################################################################################################################





################################################################################################################################
# ------------------------------------------- 大数据面板 科目可选专业数目占比 -------------------------------------------------- #
################################################################################################################################

@app.get("/selectMajorCount/{year}/{province_id}")
def get_major_count(year: str, province_id: str):
    """
        高考所选科目与可选专业数目占比
    """
    with open('json/count_choose.json', 'r', encoding='utf-8') as f:
        data_josn = json.load(f)

    subjects = data_josn.get('data')

    res = data_josn.get(adcodes.get(province_id, ''), {})
    if res != {}:
        res = res.get(year, {})
    data = []
    if res != {}:
        for key, value in res.items():
            subject = subjects[int(key)]
            num = int(value)
            item = {
                'name': subject[0][0]+subject[1][0]+subject[2][0],
                'value': num
            }
            data.append(item)

    return {
        'code': 200,
        'data': data
    }

################################################################################################################################
# ------------------------------------------- 大数据面板 科目可选专业数目占比 -------------------------------------------------- #
################################################################################################################################




################################################################################################################################
# ----------------------------------------------- 大数据面板 知乎热词占比 ------------------------------------------------------ #
################################################################################################################################

@app.get("/zhihu_hot/{year}")
def get_zhihu_hot(year: int):
    """
        根据年份获取知乎热词占比
    """
    with open('json/zhihu_hot.json', 'r', encoding='utf-8') as f:
        data_json = json.load(f)
    
    data = []
    for key, value in data_json[str(year)].items():
        data.append({
            'name': key,
            'value': int(value)
        })

    return {
        'code': 200,
        'data': data
    }

################################################################################################################################
# ----------------------------------------------- 大数据面板 知乎热词占比 ------------------------------------------------------ #
################################################################################################################################





################################################################################################################################
# ---------------------------------------------- 大数据面板 各省高校类型占比 --------------------------------------------------- #
################################################################################################################################

@app.get("/school_type/{abcode}")
def get_school_type_ratio(abcode: str):
    """
        根据省份获取该地高校类型占比
    """
    cnx = connect_sql()
    cursor = cnx.cursor()
    query_sql = ("SELECT num_985, num_211, num_double, benke, zhuanke FROM school_type WHERE province_id = %s")
    data = (abcode, )
    cursor.execute(query_sql, data)
    result = cursor.fetchone()
    cursor.close()
    cnx.close()

    return {
        "code": 200,
        "data": [
            {
                "name": "985",
                "value": result[0]
            },
            {
                "name": "211",
                "value": result[1]
            },
            {
                "name": "双一流",
                "value": result[2]
            },
            {
                "name": "本科",
                "value": result[3]
            },
            {
                "name": "专科",
                "value": result[4]
            }
        ]
    }

################################################################################################################################
# ---------------------------------------------- 大数据面板 各省高校类型占比 --------------------------------------------------- #
################################################################################################################################






################################################################################################################################
# ----------------------------------------------- 大数据面板 本科率水滴图 ------------------------------------------------------ #
################################################################################################################################

@app.get("/benke_rate/{year}/{abcode}")
def get_benke_rate(year: str, abcode: str):
    """
        根据年份、省份获取当年该地的一本上线率
    """
    cnx = connect_sql()
    cursor = cnx.cursor()
    query_sql = ("SELECT benke_rate FROM benke_rate WHERE year = %s AND province_id = %s")
    data = (year, abcode)
    cursor.execute(query_sql, data)
    result = cursor.fetchone()
    cursor.close()
    cnx.close()

    return {
        'code': 200,
        'data': {
          'value': result[0]
        },
    }

################################################################################################################################
# ----------------------------------------------- 大数据面板 本科率水滴图 ------------------------------------------------------ #
################################################################################################################################





################################################################################################################################
# ---------------------------------------------- 大数据面板 各省温度与重庆对比 ------------------------------------------------- #
################################################################################################################################

@app.get("/weather/{year}/{abcode}")
def get_weather(year: str, abcode: str):
    """
        获取重庆和指定省份的温度对比
    """
    cnx = connect_sql()
    cursor = cnx.cursor()
    query_sql = ("SELECT weather_value FROM weather WHERE year = %s AND province = %s")
    data = (year, adcodes[abcode])
    cursor.execute(query_sql, data)
    result_other = cursor.fetchone()
    result_other = eval(result_other[0])
    data = (year, '重庆')
    cursor.execute(query_sql, data)
    result_chongqing = cursor.fetchone()
    result_chongqing = eval(result_chongqing[0])

    return {   
        'code': 200,
        'data': [
          {
            'name': "1月",
            'value': int(result_chongqing[0]),
            'other': int(result_other[0]),
          },
          {
            'name': "2月",
            'value': int(result_chongqing[1]),
            'other': int(result_other[1]),
          },
          {
            'name': "3月",
            'value': int(result_chongqing[2]),
            'other': int(result_other[2]),
          },
          {
            'name': "4月",
            'value': int(result_chongqing[3]),
            'other': int(result_other[3]),
          },
          {
            'name': "5月",
            'value': int(result_chongqing[4]),
            'other': int(result_other[4]),
          },
          {
            'name': "6月",
            'value': int(result_chongqing[5]),
            'other': int(result_other[5]),
          },
          {
            'name': "7月",
            'value': int(result_chongqing[6]),
            'other': int(result_other[6]),
          },
          {
            'name': "8月",
            'value': int(result_chongqing[7]),
            'other': int(result_other[7]),
          },
          {
            'name': "9月",
            'value': int(result_chongqing[8]),
            'other': int(result_other[8]),
          },
          {
            'name': "10月",
            'value': int(result_chongqing[9]),
            'other': int(result_other[9]),
          },
          {
            'name': "11月",
            'value': int(result_chongqing[10]),
            'other': int(result_other[10]),
          },
          {
            'name': "12月",
            'value': int(result_chongqing[11]),
            'other': int(result_other[11]),
          },
        ],
      }
################################################################################################################################
# ---------------------------------------------- 大数据面板 各省温度与重庆对比 ------------------------------------------------- #
################################################################################################################################





#####################################################################################################################
# --------------------------------------高考志愿推荐系统 注册与登录功能-----------------------------------------------#
#####################################################################################################################

class User(BaseModel):
    username: str
    password: str

def check_credentials(username, password):
    cnx = connect_sql()
    cursor = cnx.cursor()
    query = ("SELECT pass_word FROM user WHERE user_name = %s")
    cursor.execute(query, (username, ))
    result = cursor.fetchone()
    cursor.close()
    cnx.close()
    if result is None:
        return False
    return result[0] == password

@app.post("/userlogin")
def user_login(user: User):
    """
        用户登录
    """
    if check_credentials(user.username, user.password):
        return {
            'code': 200,
            'data': {'message': '登录成功'}
        }
    else:
        return {
            'code': 201,
            'data': {'message': '登录失败'}
        }

class RegisterUser(BaseModel):
    username: str
    password: str
    re_password: str

@app.post("/userregister")
def user_register(user: RegisterUser):
    """
        用户注册
    """

    if user.password != user.re_password:
        return {
            'code': 201,
            'data': {'message': '两次输入密码不一致'}
        }

    cnx = connect_sql()
    cursor = cnx.cursor()
    insert_query = "INSERT INTO user (user_name, pass_word) VALUES (%s, %s)"
    data = (user.username, user.password)
    cursor.execute(insert_query, data)
    cnx.commit()
    cursor.close()
    cnx.close()

    return {
        'code': 200,
        'data': {'message': '登录成功'}
    }

#####################################################################################################################
# --------------------------------------高考志愿推荐系统 注册与登录功能-----------------------------------------------#
#####################################################################################################################





#######################################################################################################################
# --------------------------------------高考志愿推荐系统 高校信息查询功能-----------------------------------------------#
#######################################################################################################################

class School(BaseModel):
    province: str
    school_name: str
    school_class: int
    page_size: int
    page_index: int

@app.post("/getSchoolList")
def school_list(school: School):
    """
    获取高校列表
    """
    schools = []

    # 搜索功能
    if school.school_name != "":
        cursor = cnx.cursor()
        # 模糊查询
        query = ("SELECT * FROM schoolBasic WHERE name LIKE %s")
        cursor.execute(query, ("%" + school.school_name + "%",))
        results = cursor.fetchall()
        for result in results:
            schools.append(
                {
                    'id': result[0],
                    'name': result[1],
                    'province': result[2],
                    'city': result[3]
                }
            )

        return {
            'code': 200,
            'data': schools,
            'page_total': 10,
        }

    # 全部高校
    if school.school_class == -1:
        cursor = cnx.cursor()

        query = ("SELECT * FROM schoolBasic")
        cursor.execute(query)
        results = cursor.fetchall()
        for result in results:
            schools.append(
                {
                    'id': result[0],
                    'name': result[1],
                    'province': result[2],
                    'city': result[3]
                }
            )

    # 985 高校
    if school.school_class == 3:
        cursor = cnx.cursor()
        query = ("SELECT * FROM schoolBasic WHERE school_class like '1%'")
        cursor.execute(query)
        results = cursor.fetchall()
        for result in results:
            schools.append(
                {
                    'id': result[0],
                    'name': result[1],
                    'province': result[2],
                    'city': result[3]
                }
            )

    # 211 高校(不包括 985)
    if school.school_class == 2:
        cursor = cnx.cursor()
        query = ("SELECT * FROM schoolBasic WHERE school_class like '2%'")
        cursor.execute(query)
        results = cursor.fetchall()
        for result in results:
            schools.append(
                {
                    'id': result[0],
                    'name': result[1],
                    'province': result[2],
                    'city': result[3]
                }
            )

    # 双一流高校
    if school.school_class == 1:
        cursor = cnx.cursor()
        query = ("SELECT * FROM schoolBasic WHERE school_class like '%3%'")
        cursor.execute(query)
        results = cursor.fetchall()
        for result in results:
            schools.append(
                {
                    'id': result[0],
                    'name': result[1],
                    'province': result[2],
                    'city': result[3]
                }
            )

    # 其他高校
    if school.school_class == 0:
        cursor = cnx.cursor()
        query = ("SELECT * FROM schoolBasic WHERE school_class = '4'")
        cursor.execute(query)
        results = cursor.fetchall()
        for result in results:
            schools.append(
                {
                    'id': result[0],
                    'name': result[1],
                    'province': result[2],
                    'city': result[3]
                }
            )

    return {
        'code': 200,
        'data': schools,
        'page_total': 1,
    }

#######################################################################################################################
# --------------------------------------高考志愿推荐系统 高校信息查询功能-----------------------------------------------#
#######################################################################################################################





#########################################################################################################################
# --------------------------------------高考志愿推荐系统 某一高校的详情页面-----------------------------------------------#
#########################################################################################################################

class SchoolQuery(BaseModel):
    id: int

@app.post("/getSchoolDetail")
async def get_school_detail(query: SchoolQuery):
    """
        某一高校的详细信息
    """
    cursor = cnx.cursor()
    query_sql = ("SELECT * FROM schoolDetails WHERE id = %s")
    data = (query.id,)
    cursor.execute(query_sql, data)
    results = cursor.fetchall()

    for result in results:
        return {
            "code": 200,
            "data": {
                "id": result[0],                    # 高校 id    / key
                "name": result[1],                  # 高校名称    / name
                "nature": result[2],                # 高校办学性质 / school_nature_name
                "level": result[3],                 # 高校等级    / level_name
                "f985": result[4],                  # 是否为985   / f985
                "f211": result[5],                  # 是否为211   / f211
                "dual_class": result[6],            # 是否为为双一流 / dual_class_name
                "study": result[7],                 # 学习指数 / study
                "life": result[8],                  # 生活指数 / life
                "job": result[9],                   # 就业指数 / job
                "comprehensive": result[10],        # 综合评分 / comprehensive
                "content": result[11],              # 简介 / content
                "job_class": eval(result[12]),      # 毕业生去向
                "job_ratio": eval(result[13]),      # 去向比例
                "num_lab": result[14],              # 重点实验室数量 /num_lab
                "num_subject": result[15],          # 国家重点学科数量 /num_subject
                "num_master": result[16],           # 硕士点数量 /num_master
                "num_doctor": result[17],           # 博士点数量 /num_doctor
                "jobrate": result[18],              # 就业率       /job
                "postgraduate": result[19],         # 深造率  /postgraduate
                "abroad": result[20],               # 出国率         /abroad
                "men_rate": result[21],             # 男生比例     / men_rate
                # ... 其他字段
            }
        }


class SpQuery(BaseModel):
    id: int
    year: str
    province: str

@app.post("/getEachSp")
async def get_each_sp(query: SpQuery):
    """
        某一高校各个年份的专业分数线信息
    """
    cnx = connect_sql()
    cursor = cnx.cursor()
    query_sql = ("SELECT name FROM schoolBasic WHERE id = %s")
    data_sql = (query.id, )
    cursor.execute(query_sql, data_sql)
    results= cursor.fetchone()
    school_name=results[0]
    where = "province_name = '{}' and school_name = '{}' and year = '{}'".format(query.province, school_name, query.year)
    q = "select major_name,batch_name,require_str,min_grade,min_rank from grade_plan where " + where
    cursor.execute(q)
    results = cursor.fetchall()
    cursor.close()
    cnx.close()

    data = []
    for result in results:
        item = {
            "name": result[0],
            "batch": result[1],
            "score": result[3],
            "rank":  result[4],
            "require":  result[2],
        }
        if item['score'] != 0:
            data.append(item)

    return {
        "code": 200,
        "data": data
    }

#########################################################################################################################
# --------------------------------------高考志愿推荐系统 某一高校的详情页面-----------------------------------------------#
#########################################################################################################################





#########################################################################################################################
#---------------------------------------高考志愿推荐系统 专业信息查询功能-------------------------------------------------#
#########################################################################################################################

class Special(BaseModel):
    name: str
    level: str
    class_: str
    page_size: int
    page_index: int

@app.post("/getSpecialList")
async def get_special_list(special: Special):
    """
        获取专业列表
    """
    cnx = connect_sql()
    cursor = cnx.cursor()

    if special.name != "":
        query_sql = ("SELECT * FROM majorBasic WHERE name LIKE %s")
        data = ("%" + special.name + "%", )
    else:
        if special.class_ == '全部':
            query_sql = ("SELECT * FROM majorBasic where sp_level = %s")
            data = (special.level, )
        else:
            query_sql = ("SELECT * FROM majorBasic WHERE sp_level = %s AND belong = %s")
            data = (special.level, special.class_)
    
    cursor.execute(query_sql, data)
    results = cursor.fetchall()
    cursor.close()
    cnx.close()

    special_list = []
    for result in results:
        sp = {
            'id': result[0],
            'name': result[1],
            'hot': result[2],
            'code': result[0],
            'year': result[3],
            'degree': result[4],
        }
        if sp['name'] != '未知':
            special_list.append(sp)

    return {
        'code': 200,
        'data': special_list,
        'page_total': 1,
    }

class SpecialQuery(BaseModel):
    id: str

@app.post("/getSpecialDetail")
async def get_special_detail(query: SpecialQuery):
    """
        获取专业详细信息
    """
    cnx = connect_sql()
    cursor = cnx.cursor()
    query_sql = ("SELECT * FROM majorDetails WHERE id = %s")
    data_sql = (query.id, )
    cursor.execute(query_sql, data_sql)
    result = cursor.fetchone()
    cursor.close()
    cnx.close()

    data = {
        "id": result[0],
        "name": result[1],
        "hot": result[2],
        "code": result[0],
        "year": result[3],
        "degree": result[4],
        "is_what": result[5],
        "learn_what": result[6],
        "do_what": result[7],
        "content": result[8],
        "recommend_subject": result[9],
        "postgraduate": result[10],
        "subject": result[11],
        "people": result[12],
        "Overall_satisfaction": result[13],      
        "EducationConditions_satisfaction": result[14],  
        "TeachingQuality_satisfaction": result[15], 
        "JobRate_satisfaction": result[16],  
        "salary_self": eval(result[17]),
        "salary_average": eval(result[18]),
        "Rate1": result[19],
        "Rate2": result[20],
        "Rate3": result[21],
        "Rate4": result[22],
        "Rate5": result[23],
        "Rate6": result[24],
        "Job1": result[25],
        "Job2": result[26],
        "Job3": result[27],
        "Job4": result[28],
        "Job5": result[29],
        "Job6": result[30],
        "rate1": result[31],
        "rate2": result[32],
        "rate3": result[33],
        "rate4": result[34],
        "rate5": result[35],
        "region1": result[36],
        "region2": result[37],
        "region3": result[38],
        "region4": result[39],
        "region5": result[40],
        "men_rate": result[41]
        }

    return {
        "code": 200,
        "data": data
    }

#########################################################################################################################
#---------------------------------------高考志愿推荐系统 专业信息查询功能-------------------------------------------------#
#########################################################################################################################





#########################################################################################################################
#----------------------------------------高考志愿推荐系统 志愿推荐功能----------------------------------------------------#
#########################################################################################################################

def subject_sort(subjects):
    order = {"物理": 1, "历史": 2, "化学": 3, "政治": 4, "生物": 5, "地理": 6, "技术": 7}
    if not all(subject in order.keys() for subject in subjects):
        raise ValueError()
    return sorted(subjects, key=lambda x: order.get(x, float('inf')))

def index_sim_search(province_name, choose_list, rank, type="高风险", school_province_name="",
                     is_double_first_class=False, is_985=False, is_211=False):
    con = connect_sql()
    cursor = con.cursor()
    if type == "高风险":
        rank_min = int(max(rank - 20000 - rank * 0.1, 0))
        rank_max = rank
    elif type == "中风险":
        rank_min = int(max(rank - 10000 - rank * 0.1, 0))
        rank_max = int(rank + 10000 + rank * 0.1)
    else:
        rank_min = int(rank + rank * 0.1)
        rank_max = int(rank + 20000 + rank * 0.1)
    columns = ["school_name", "min_rank", "choose1", "choose2", "choose3"]
    choose_list = subject_sort(choose_list)
    rank_where = "min_rank >= {} and min_rank <= {}".format(rank_min, rank_max)
    where = []
    where.append("province_name = '{}'".format(province_name))
    if school_province_name != "" and school_province_name != "全部":
        where.append("school_province_name = '{}'".format(school_province_name))
    if is_double_first_class:
        where.append("is_dual_class = 1")
    if is_985:
        where.append("is_985 = 1")
    if is_211:
        where.append("is_211 = 1")
    if len(where) > 0:
        where = rank_where + " and " + " and ".join(where)
    else:
        where = rank_where
    q1 = "select {} from grade_sim_index where {}".format(",".join(columns), where)
    where = []
    where.append("choose1 is null or choose1=''")
    where.append("(choose1 = '{}' and (choose2 is null or choose2=''))".format(choose_list[2]))
    where.append(
        "(choose1 = '{}' and "
        "(choose2 is null or choose2='' or choose2='{}'))".format(choose_list[1], choose_list[2]))
    where.append("(choose1 = '{}' and "
                 "(choose2 is null or choose2='' or "
                 "(choose2='{}' and "
                 "(choose3 is null or choose3='' or choose3='{}')) or "
                 "(choose2='{}' and (choose3 is null or choose3=''))))".format(choose_list[0], choose_list[1],
                                                                               choose_list[2], choose_list[2]))

    q2 = "select school_name, avg(min_rank) as m from ({}) as t1 where {} GROUP BY school_name".\
        format(q1," or ".join(where))
    q = "select * from ({}) as t2 order by abs(m-{}) LIMIT 30".format(q2, rank)
    cursor.execute(q)
    results = cursor.fetchall()
    return results

class SchoolRecommend(BaseModel):
    type_: str
    score: int
    rank: int
    studentProvince: str
    risk: int
    province: str
    class_: int
    page_size: int
    page_index: int

@app.post("/recommend")
def recommend_school(school: SchoolRecommend):
    """
        个性化志愿推荐
    """
    if school.type_ == "":
        return {
            'code': 200,
            'data': [{
                'id': '119',
                'name': '未知',
                'level': '',
                'rank': 0,
                'possible': '',
            }, ],
            'page_total': 1
        }

    subject_choice = school.type_.split(',')

    is985 = False
    is211 = False
    isDouble = False
    if school.class_ == 3:
        is985 = True
    elif school.class_ == 2:
        is211 = True
    elif school.class_ == 1:
        isDouble = True

    possible = ''
    if school.risk == 0:
        res = index_sim_search(school.studentProvince, subject_choice, school.rank, "低风险", school.province, isDouble, is985, is211)
        possible = '高'
    if school.risk == 1:
        res = index_sim_search(school.studentProvince, subject_choice, school.rank, "中风险", school.province, isDouble, is985, is211)
        possible = '中'
    if school.risk == 2:
        res = index_sim_search(school.studentProvince, subject_choice, school.rank, "高风险", school.province, isDouble, is985, is211)
        possible = '低'
    
    data = []
    for item in res:
        cnx = connect_sql()
        cursor = cnx.cursor()
        query_sql = ("SELECT id, school_class FROM schoolBasic WHERE name = %s")
        data_sql = (item[0], )
        cursor.execute(query_sql, data_sql)
        result= cursor.fetchone()
        cursor.close()
        cnx.close()

        level = ''
        if result[1][0] == '1':
            level = '985 211 双一流'
        elif result[1][0] == '2' and len(result[1]) > 1:
            level = '211 双一流'
        elif result[1][0] == '2' and len(result[1]) == 1:
            level = '211'
        elif result[1][0] == '3':
            level = '双一流'

        school_choosen = {
            'id': result[0],
            'name': item[0],
            'level': level,
            'rank': int(item[1]),
            'possible': possible,
        }

        data.append(school_choosen)    

    return {
        'code': 200,
        'data': data,
        'page_total': 1,
    }

#########################################################################################################################
#----------------------------------------高考志愿推荐系统 志愿推荐功能----------------------------------------------------#
#########################################################################################################################





#########################################################################################################################
#--------------------------------------------高考志愿推荐系统 志愿表-----------------------------------------------------#
#########################################################################################################################

class Volunteer(BaseModel):
    username: str
    name: str
    possible: str

@app.post("/addselect")
def add_volunteer_table(volunteer: Volunteer):
    """
        向志愿表中添加数据
    """
    cnx = connect_sql()
    cursor = cnx.cursor()
    insert_query = "INSERT INTO volunteer (username, school, possible) VALUES (%s, %s, %s)"
    data = (volunteer.username, volunteer.name, volunteer.possible)
    cursor.execute(insert_query, data)
    cnx.commit()
    cursor.close()

    return {
        'code': 200,
        'message': ''
    }

class UserSelect(BaseModel):
    username: str

@app.post("/getselect")
def get_school_list(user: UserSelect):
    """
        获取志愿表中的数据
    """
    cnx = connect_sql()
    cursor = cnx.cursor()
    query_sql = ("SELECT school, possible FROM volunteer WHERE username = %s")
    data = (user.username, )
    cursor.execute(query_sql, data)
    results = cursor.fetchall()
    cursor.close()
    cnx.close()

    data = []
    for result in results:
        data.append({
            'name': result[0],
            'possible': result[1]
            })
        
    return {
        'code': 200,
        'data': data
    }

class DelSelect(BaseModel):
    username: str
    name: str
    
@app.post("/delselect")
def del_volunteer(delV: DelSelect):
    """
        删除志愿表中的数据
    """
    cnx = connect_sql()
    cursor = cnx.cursor()
    delete_sql = ("DELETE from volunteer WHERE username = %s and school = %s")
    data = (delV.username, delV.name)
    cursor.execute(delete_sql, data)
    cnx.commit()
    cursor.close()
    cnx.close()

    return {
        'code': 200,
        'message': '删除成功'
    }

#########################################################################################################################
#--------------------------------------------高考志愿推荐系统 志愿表-----------------------------------------------------#
#########################################################################################################################
